package it.ap.sa.core.dao.strategy;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import org.hibernate.cfg.reveng.DelegatingReverseEngineeringStrategy;
import org.hibernate.cfg.reveng.ReverseEngineeringStrategy;
import org.hibernate.cfg.reveng.TableIdentifier;

import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryCollection;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.MultiLineString;
import com.vividsolutions.jts.geom.MultiPoint;
import com.vividsolutions.jts.geom.MultiPolygon;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;

@SuppressWarnings({ "rawtypes", "serial" })
public class PostgisStrategy extends DelegatingReverseEngineeringStrategy {
	
	private static final String URL = "jdbc:postgresql://localhost:5432/acquariumdb";
	private static final String USERNAME = "postgres";
	private static final String PASSWORD = "postgres";

	@Override
	public boolean excludeColumn(TableIdentifier identifier, String columnName) {
	 
	    if ( columnName.toUpperCase().startsWith("USERINS") 
	            || columnName.toUpperCase().startsWith("USERMOD") 
	            || columnName.toUpperCase().startsWith("DATEINS")
	            || columnName.toUpperCase().startsWith("DATEMOD")
	        ) {
	        return true;
	    }
	    return false;
	}
	
	//geometry type to class map
	final static Map<String, Class> TYPE_TO_CLASS_MAP = new HashMap<String, Class>() {
        {
            put("GEOMETRY", Geometry.class);
            put("GEOGRAPHY", Geometry.class);
            put("POINT", Point.class);
            put("POINTM", Point.class);
            put("LINESTRING", LineString.class);
            put("LINESTRINGM", LineString.class);
            put("POLYGON", Polygon.class);
            put("POLYGONM", Polygon.class);
            put("MULTIPOINT", MultiPoint.class);
            put("MULTIPOINTM", MultiPoint.class);
            put("MULTILINESTRING", MultiLineString.class);
            put("MULTILINESTRINGM", MultiLineString.class);
            put("MULTIPOLYGON", MultiPolygon.class);
            put("MULTIPOLYGONM", MultiPolygon.class);
            put("GEOMETRYCOLLECTION", GeometryCollection.class);
            put("GEOMETRYCOLLECTIONM", GeometryCollection.class);
            put("BYTEA", byte[].class);
        }
    };

    //geometry class to type map
    final static Map<Class, String> CLASS_TO_TYPE_MAP = new HashMap<Class, String>() {
        {
            put(Geometry.class, "GEOMETRY");
            put(Point.class, "POINT");
            put(LineString.class, "LINESTRING");
            put(Polygon.class, "POLYGON");
            put(MultiPoint.class, "MULTIPOINT");
            put(MultiLineString.class, "MULTILINESTRING");
            put(MultiPolygon.class, "MULTIPOLYGON");
            put(GeometryCollection.class, "GEOMETRYCOLLECTION");
            put(byte[].class, "BYTEA");
        }
    };
    
    /**
     * ritorn la classe di mapping per la geometria
     * @return
     * @throws Exception
     */
	public static Class calculateType(String catalog,String schema,String tableName,String columnName) throws Exception {
		Connection connection = null;
		Class geometryClass=null;
		try {
			//Class.forName(DRIVER);
			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

			DatabaseMetaData metadata = connection.getMetaData();
			ResultSet resultSet = metadata.getColumns(catalog, schema, tableName, columnName);
			if (resultSet.next()) {//prendo i primo dovrebbe essere uno!?
				String name = resultSet.getString("COLUMN_NAME");
				String type = resultSet.getString("TYPE_NAME");
				int size = resultSet.getInt("COLUMN_SIZE");
				
				 if ("geometry".equalsIgnoreCase(type)) {
			            type = lookupGeometryType(resultSet, connection, "geometry_columns", "f_geometry_column");
			              geometryClass = (Class) TYPE_TO_CLASS_MAP.get(type.toUpperCase());
			        }  
				System.err.println("Column name: [" + name + "]; type: [" + type + "]; size: [" + size + "]"+ "class:"+geometryClass);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connection.close();
		}
		return geometryClass;
	}
	
	//--------------------------------
	public static String lookupGeometryType(ResultSet columnMetaData, Connection cx, String gTableName, String gColumnName) throws SQLException {
        
        // grab the information we need to proceed
        String tableName = columnMetaData.getString("TABLE_NAME");
        String columnName = columnMetaData.getString("COLUMN_NAME");
        String schemaName = columnMetaData.getString("TABLE_SCHEM");

        // first attempt, try with the geometry metadata
        Statement statement = null;
        ResultSet result = null;
        
        try {
            String sqlStatement = 
            		"SELECT TYPE FROM " + gTableName + 
            		" WHERE F_TABLE_SCHEMA = '" + schemaName + "' AND F_TABLE_NAME = '" + tableName + "' AND " + gColumnName + " = '" + columnName + "'";

            statement = cx.createStatement();
            result = statement.executeQuery(sqlStatement);

            if (result.next()) {
                return result.getString(1);
            }
        } finally {
//            dataStore.closeSafe(result);
//            dataStore.closeSafe(statement);
        }

        return null;
    }
	
	public PostgisStrategy(ReverseEngineeringStrategy delegate) {
		 super(delegate);
	}

	@Override
	public String columnToHibernateTypeName(TableIdentifier table, String columnName, int sqlType, int length, int precision, int scale, boolean nullable, boolean generatedIdentifier) {
		// TODO Auto-generated method stub
		if(sqlType==1111){//geometry
			//return "com.vividsolutions.jts.geom.LineString";
			String ret="org.json.JSONObject";//default to Point or Geometry !?
			try {
				Class geomClass=calculateType(table.getCatalog(),table.getSchema(),table.getName(), columnName);
				if(geomClass!=null){
					ret=geomClass.getName();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return ret;
		}else{
			return super.columnToHibernateTypeName(table, columnName, sqlType, length, precision, scale, nullable, generatedIdentifier);
		}
	}
}